package com.zh.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
	
	private  final static  String url = 
			"jdbc:mysql://localhost:3306/basic?useSSL=true&useUnicode=true&characterEncoding=utf8";
	private  final static String name = "root";
	private  final static String password = "123456";
	
	
	static {
		//1.加载驱动
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取连接
	 * @return 数据库的连接
	 */
	public static Connection getConnection() {
		//2.创建connection（建立java应用与数据库的连接）
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, name, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
					
	}
	
	/**
	 * 释放资源
	 * @param statement
	 * @param conn
	 */
	public static void release(Statement statement ,Connection conn) {
		try {
			statement.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * 执行sql语句
	 * @param sql
	 */
	public static void excute(String sql) {
		Connection conn = getConnection();
		//3.获取statement对象
		Statement statement = null;
		try {
			statement = conn.createStatement();
			//4.执行sql语句
			statement.execute(sql);
			//5.处理结果集
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			release(statement, conn);
		}
		
	}
	
	public static  List excuteQuery(String sql) {
		Connection conn = getConnection();
		Statement statement = null;
		List<Map> result = new ArrayList<>();
		try {
			statement = conn.createStatement();
			//执行sql查询语句
			ResultSet resultSet = statement.executeQuery(sql);
			
			//获取数据表的元信息数据（比如表中的列：id，name，）
			ResultSetMetaData metaData = resultSet.getMetaData();
			//获取表列的总数
			int count = metaData.getColumnCount();
			// 遍历结果集（每一行）
			while(resultSet.next()) {
				Map temp = new HashMap<>();
				//遍历每一列
				for(int i = 1 ; i<= count;i++) {
					temp.put(metaData.getColumnLabel(i), resultSet.getString(i));
				}
//				把每行数据导入到result中
				result.add(temp);
			}	
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			release(statement, conn);
		}
		//返回
		return result;
		
	}
	
	
}
